﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace DAL
{
    /// <summary>
    /// 得到每一个题的得分情况 李少然
    /// </summary>
    public class TotalScoreDAL
    {
        private SQLHelper sqlhelper = null;

        public TotalScoreDAL()
        {
            sqlhelper = new SQLHelper();
        }
        TBToList<StudentInfoEntity> dtToList = new TBToList<StudentInfoEntity>();

        #region 调出word分数,修改人李少然
        //word分数
        public DataTable WordTotalScore(StudentInfoEntity studentinfo)
        {
            String which = WhichWordRecored(studentinfo);
            DataTable dt = new DataTable();
            string sql = "SELECT StudentID AS '学号', ExamAnswer AS '学生答案', RightAnswer AS '正确答案', Fration AS '分数', QuestionContent AS '题目', [TimeStamp] AS '考试时间' FROM WordQuestionRecordEntity_"+ which +"  WHERE [TimeStamp] = ( SELECT MAX ([TimeStamp]) FROM WordQuestionRecordEntity_"+ which +"  WHERE StudentID = @studentID OR StudentID IN ( SELECT StudentID FROM StudentInfoEntity WHERE studentName LIKE @studentName ))";
            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("@studentID",studentinfo.studentID),
                new SqlParameter("@studentName",studentinfo.studentName)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 调出ppt分数 修改人：李少然
        //ppt分数
        public DataTable PptTotalScore(StudentInfoEntity studentinfo)
        {
            String which = WhichWordRecored(studentinfo);
            DataTable dt = new DataTable();
            string sql = "SELECT StudentID AS '学号', ExamAnswer AS '学生答案', RightAnswer AS '正确答案', Fration AS '分数', QuestionContent AS '题目', [TimeStamp] AS '考试时间' FROM PptQuestionRecordEntity_"+ which +"  WHERE [TimeStamp] = ( SELECT MAX ([TimeStamp]) FROM PptQuestionRecordEntity_"+ which +"  WHERE StudentID = @studentID OR StudentID IN ( SELECT StudentID FROM StudentInfoEntity WHERE studentName LIKE @studentName ))";
            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("@studentID",studentinfo.studentID),
                new SqlParameter("@studentName",studentinfo.studentName)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion


        #region 调出选择分数 修改人：王荣晓
        //选择分数
        public DataTable SelectTotalScore(StudentInfoEntity studentinfo)
        {
            String which = WhichWordRecored(studentinfo);
            DataTable dt = new DataTable();
            string sql = "SELECT QuestionID AS '题号', StudentID AS '学号', ExamAnswer AS '学生答案', RightAnswer AS '正确答案', Fration AS '分数', QuestionContent, OptionA, OptionB, OptionC, OptionD, [TimeStamp] AS '考试时间' FROM SelectQuestionRecordEntity_" + which +" WHERE [TimeStamp] =(SELECT MAX([TimeStamp]) FROM SelectQuestionRecordEntity_"+ which +" WHERE StudentID = @studentID OR StudentID IN (SELECT studentID FROM StudentInfoEntity WHERE studentName LIKE @studentName))";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",studentinfo.studentID),
                new SqlParameter("@studentName",studentinfo.studentName)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;

        }
        #endregion

        #region 进行跳转，根据学号，时间戳查询学生的答题答案——陈新月——2018-03-27
        public DataTable SelectOneRecord(StudentInfoEntity studentInfo,SelectQuestionRecordEntity scoreInfo)
        {
            String which = WhichWordRecored(studentInfo);
            DataTable dt = new DataTable();
            string sql= "select StudentID as '学号',ExamAnswer as '学生答案',RightAnswer as '正确答案',Fration as '分数',TimeStamp as '考试时间' from SelectQuestionRecordEntity_" + which + "   where StudentID like @studentID and TimeStamp like @TimeStamp";
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@studentID",'%'+studentInfo.studentID+'%'),
                new SqlParameter("@TimeStamp",'%'+scoreInfo.TimeStamp+'%')
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 调出Windows分数
        public DataTable WindowsScore(StudentInfoEntity studentinfo)
        {
            String which = WhichWordRecored(studentinfo);
            DataTable dt = new DataTable();
            string sql = "SELECT studentID AS '学号', examAnswer AS '学生答案', correctAnswer AS '正确答案', fraction AS '分数', questionContent AS '题目',timeStamp AS '考试时间' FROM WinQuestionRecordEntity_"+ which +"  WHERE timeStamp = ( SELECT MAX (timeStamp) FROM WinQuestionRecordEntity_"+ which +"  WHERE studentID = @studentID OR studentID IN ( SELECT StudentID FROM StudentInfoEntity WHERE studentName LIKE @studentName ))";
            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("@studentID",studentinfo.studentID),
                new SqlParameter("@studentName",studentinfo.studentName)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 调出IE分数
        //ie分数
        public DataTable IETotalScore(StudentInfoEntity studentinfo)
        {
            String which = WhichWordRecored(studentinfo);
            DataTable dt = new DataTable();
            string sql = " SELECT studentID AS '学号', examAnswer AS '学生答案', correctAnswer AS '正确答案', fraction AS '分数', QuestionContent AS '题目', timeStamp AS '考试时间' FROM IEQuestionRecordEntity_"+ which +"  WHERE timeStamp = ( SELECT MAX (timeStamp) FROM IEQuestionRecordEntity_"+ which +"  WHERE StudentID = @studentID OR StudentID IN ( SELECT StudentID FROM StudentInfoEntity WHERE studentName LIKE @studentName ))";
            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("@studentID",studentinfo.studentID),
                new SqlParameter("@studentName",studentinfo.studentName)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 调出Excel分数
        //excel分数
        public DataTable ExcelTotalScore(StudentInfoEntity studentinfo)
        {
            String which = WhichWordRecored(studentinfo);
            DataTable dt = new DataTable();
            string sql = "SELECT StudentID AS '学号', ExamAnswer AS '学生答案', CorrectAnswer AS '正确答案', Fration AS '分数', QuestionContent AS '题目', [TimeStamp] AS '考试时间' FROM ExcelQuestionRecordEntity_"+ which +"  WHERE [TimeStamp] = ( SELECT MAX ([TimeStamp]) FROM ExcelQuestionRecordEntity_"+ which +"  WHERE StudentID = @studentID OR StudentID IN ( SELECT StudentID FROM StudentInfoEntity WHERE studentName LIKE @studentName ))";
            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("@studentID",studentinfo.studentID),
                new SqlParameter("@studentName",studentinfo.studentName)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 看看向哪个数据库表中进行填写数据  邱慕夏  2015年11月23日14:26:41
        /// <summary>
        /// 看看向哪个数据库表中进行填写数据
        /// </summary>
        /// <param name="studentrecord"></param>
        /// <returns></returns>
        public String WhichWordRecored(StudentInfoEntity studentrecord)
        {
            DataTable dt = new DataTable();
            string sql = "select collegeId  from StudentInfoEntity where studentID like @studentID or studentID in(select studentID from StudentInfoEntity where studentName like @studentName)";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",'%'+ studentrecord.studentID +'%' ),
                new SqlParameter("@studentName",'%'+studentrecord.studentName +'%')
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            String which = dt.Rows[0][0].ToString();
            return which;
        }
        #endregion

        #region 根据学号或者姓名查询学生的信息
        public DataTable SelectStudentinfo(StudentInfoEntity studentinfo)
        {
            DataTable dt = new DataTable();
            string sql = "select * from StudentInfoEntity where studentID like @studentID or studentName like @studentName";
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@studentID",'%'+ studentinfo.studentID+'%'),
                new SqlParameter("@studentName",'%'+ studentinfo.studentName+'%')
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 根据学院ID查询学院姓名
        public DataTable SelectCollageName(StudentInfoEntity studentinfo)
        {
            DataTable dt = new DataTable();
            string sql = "select collegeName from CollegeEntity where collegeID=@collegeID";
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@collegeID",studentinfo.CollegeID)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion
    }
}

